package edu.upenn.cis.cis550.hw3.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;

import edu.upenn.cis.cis550.hw3.client.UpdateTables;

public class UpdateTablesImpl extends RemoteServiceServlet implements
    UpdateTables {
	
	Connection connection;

    private static final long serialVersionUID = 1L;

    /**
     * Constructor -- initialize our source tables and the object
     */
    public UpdateTablesImpl() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String serverName = "fling.seas.upenn.edu:3306";
            String database = "silviamo";
            String url = "jdbc:mysql://" + serverName + "/" + database;
            String user = "silviamo";
            String password = "dbproject";

            connection = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
    }

	  /**
     * Start of all updating queries
     */
    public String addUser(String firstName, String lastName,
            String email, String dob, String gender) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO User VALUES (\"0\",\"");
        query.append(firstName);
        query.append(delim);
        query.append(lastName);
        query.append(delim);
        query.append(email);
        query.append(delim);
        query.append(dob);
        query.append(delim);
        query.append(gender);
        query.append("\")");        
        String queryString = query.toString();
        System.out.println(queryString);
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString, Statement.RETURN_GENERATED_KEYS);
            ResultSet rs = statement.getGeneratedKeys();
            if(rs.next()){
                int uid = rs.getInt(1);
                System.out.println(uid);
                return Integer.toString(uid);
            }
        } catch (SQLException e) {
            return e.getMessage();
        }
        return queryString;
    }

    public String addCircle(int userID, String name) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Circle VALUES (");
        query.append(userID);
        query.append(delim);
        query.append(name);
        query.append("\");");
        
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString);
            return "Sucess! New circle created"; 
        } catch (SQLException e) {
            return e.getMessage();
        }
    }

    public String addFriend(int userID, int friendID, String circleName) {
        System.out.println("here");
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Friend VALUES (\"");
        query.append(userID);
        query.append(delim);
        query.append(friendID);
        query.append(delim);
        query.append(circleName);
        query.append("\");");
        String queryString = query.toString();
        System.out.println(queryString);
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString);
            return "Sucess! New friend added";
        } catch (SQLException e) {
            return e.getMessage();
        }

    }

    public String addPhoto(int userID, String url, String description, String photoTime, int visibility, ArrayList<String> ids) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Photo (photoID, userID,url,description,visibility) VALUES (\"");
        query.append("0");
        query.append(delim);       
        query.append(userID);
        query.append(delim);
        query.append(url);
        query.append(delim);
        query.append(description);
        query.append(delim);
        //query.append("0");
        //query.append(delim);
        query.append(visibility);
        query.append("\")");
        String queryString = query.toString();
        System.out.println(queryString);
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString, Statement.RETURN_GENERATED_KEYS);
            ResultSet rs = statement.getGeneratedKeys();
            if(rs.next()) {
                int photoID = rs.getInt(1);
                if(visibility == 1){
                	updateVisibility("VisibUsers",photoID,ids);
                } else if(visibility == 2){
                	updateVisibility("VisibCircles",photoID,ids);
                }     
            }
            return "Sucess! New photo added";
        } catch (SQLException e) {
            return e.getMessage();
        }
    }

    public void updateVisibility(String table, int photoID, ArrayList<String> ids) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO " + table + " VALUES (");
        query.append(photoID);
        query.append(delim);
        Iterator<String> i = ids.iterator();
        while(i.hasNext()){
        	query.append(photoID);
        	query.append(delim);
        	query.append(i.next());
        	query.append(delim);
        }        
        query.append("\");");       
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString);
        } catch (SQLException e) {
        }
    }
    
    public String addTag(int photoID, String tag) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Tag VALUES (\"");
        query.append(photoID);
        query.append(delim);
        query.append(tag);
        query.append("\");");
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString);
            updateKeywords(tag);
            return "Sucess! New tag added";
        } catch (SQLException e) {
            return e.getMessage();
        }
    }
    
    public void updateKeywords(String tag) {
    	String[] a1 = tag.trim().split(" ",0);
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO `Keywords` (`word`) VALUES");       
        for(int i =0; i < a1.length;i++){
        	ResultSet keywords = getKeywords(a1[i]);
        	try {
				if(keywords.first()){
				    query.append("('" + a1[i] + "')");
				    if(i != a1.length)
				       query.append(",");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        query.append(";");        
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public ResultSet getKeywords(String word) throws IllegalArgumentException {
        try {
            Statement statement = connection.createStatement();

            if (statement
                    .execute("select * from Keywords where Keywords.word = " + word)) {         	
                return statement.getResultSet();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public String addRating(int userID, int photoID, int score) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Rating VALUES (\"");
        query.append(userID);
        query.append(delim);
        query.append(photoID);
        query.append(delim);
        query.append(score);
        query.append("\");");
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString);
            return "Success! New rating added";
        } catch (SQLException e) {
            return e.getMessage();
        }
    }

    public String addComment(int userID, int photoID, String comment, String timeStamp) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Comment VALUES (");
        query.append(userID);
        query.append(delim);
        query.append(photoID);
        query.append(delim);
        query.append(comment);
        query.append(delim);
        query.append(timeStamp);
        query.append("\");");
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString);
            return "Success! New comment added";
        } catch (SQLException e) {
            return e.getMessage();
        }
    }

    public void addAddress(int userID, String street, String city,
            String state, String zip) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Address VALUES (");
        query.append(userID);
        query.append(delim);
        query.append(street);
        query.append(delim);
        query.append(city);
        query.append(delim);
        query.append(state);
        query.append(delim);
        query.append(zip);
        query.append("\");");
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void addInterest(String interest) {
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Interest VALUES (");
        query.append(interest);
        query.append("\");");
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();

            statement.execute(queryString);
        } catch (SQLException e) {

            e.printStackTrace();
        }
    }

    public void addInterestToUser(int userID, int interestID) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO UserInterest VALUES (");
        query.append(userID);
        query.append(delim);
        query.append(interestID);
        query.append("\");");
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();

            statement.execute(queryString);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void addLogin(int userID, String password) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Login VALUES (");
        query.append(userID);
        query.append(delim);
        query.append(password);
        query.append("\");");
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();

            statement.execute(queryString);
        } catch (SQLException e) {

            e.printStackTrace();
        }
    }

    public boolean addStudent(int userID, int year, String major, float GPA,
            String college) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Student VALUES (\"");
        query.append(userID);
        query.append(delim);
        query.append(year);
        query.append(delim);
        query.append(major);
        query.append(delim);
        query.append(GPA);
        query.append(delim);
        query.append(college);
        query.append("\");");
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean addProfessor(int userID, String office, String researchArea,
            String title, String college) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Professor VALUES (\"");
        query.append(userID);
        query.append(delim);
        query.append(office);
        query.append(delim);
        query.append(researchArea);
        query.append(delim);
        query.append(title);
        query.append(delim);
        query.append(college);
        query.append("\");");
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();
            statement.execute(queryString);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public void addAdvisesRelationship(int studentID, int profID, int years) {
        String delim = "\",\"";
        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO Advises VALUES (");
        query.append(profID);
        query.append(delim);
        query.append(studentID);
        query.append(delim);
        query.append(years);
        query.append("\");");
        String queryString = query.toString();
        try {
            Statement statement = connection.createStatement();

            statement.execute(queryString);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
	
    public String getUsers() {
        try {
            Statement statement = connection.createStatement();

            statement.execute("select * from user");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "this works";
    }
}
